# -*- coding: utf-8 -*-
import sys
sys.path.append("..")
import os
import pymysql
import time
import pandas as pd


class UpdateNewestInfo(object):
  """
  """

  #写入文件
  def insert_file(self, fileName, dataList):
    f = "d:\\data\\mysql\\%s.sql" %(fileName)
    try:
      #只需要将之前的”w"改为“a"即可，代表追加内容
      with open(f, "a" ,encoding="utf-8") as file:
        for json in dataList:
          file.write(str(json) + "\n")
    except Exception as e:
      print(e)

    return True

  #批量操作
  def exec_batch(self, connect, cursor, sqlList):
    connect.ping(reconnect = True)
    count = 0
    for sql in sqlList :
      try:
        ret = cursor.execute(sql)
        count += ret
      except Exception as e:
        print(sql)
    # 提交，不进行提交无法保存到数据库
    connect.commit()
    #connect.close()

    return count

  #统计记录数
  def count_data(self, connect, cursor, query_sql):
    connect.ping(reconnect = True)
    try:
      cursor.execute(query_sql)
      queryResult = cursor.fetchone()

    except Exception as e:
      print(e)
    #finally:

    return queryResult

  #查询数据
  def query_data(self, connect, cursor, query_sql):
    connect.ping(reconnect = True)
    try:
      cursor.execute(query_sql)
      queryResult = cursor.fetchall()

    except Exception as e:
      print(e)
    #finally:

    return queryResult

  #转换数据
  def convert_data(self, queryResult, tableName):
    convertList = []
    for x in queryResult:
      id = x[0]
      gd_city = x[2]
      if gd_city == None :
        gd_city = ''

      gd_county = x[3]
      if gd_county == None :
        gd_county = ''

      gd_lnglat = x[4]
      if gd_lnglat == None :
        gd_lnglat = ''

      gd_office_name = x[5]
      if gd_office_name == None :
        gd_office_name = ''

      is_accurate = x[6]
      if is_accurate == None :
        is_accurate = ''

      sql = "update " + tableName + " set gd_city='%s', gd_county='%s', gd_lnglat='%s', gd_office_name='%s', is_accurate='%s' where id=%d " % (gd_city, gd_county, gd_lnglat, gd_office_name, is_accurate, id)
      #print(sql)
      convertList.append(sql)

    return convertList

  #执行操作
  def exec(self, odsdbConn, odsdbCursor, count_sql, query_sql, platform, tableName):
    totalCount = obj.count_data(odsdbConn, odsdbCursor, count_sql)[0]
    print("总记录数", totalCount)
    if totalCount == 0 :
      totalCount = 1;

    pageSize = 1000
    pages = int((totalCount-1)/pageSize) + 1
    row = 0

    for pageNum in range(pages):
      #break
      query_sql_tmp = query_sql %(platform, (pageNum * pageSize), pageSize)
      #print(query_sql_tmp)
      result = obj.query_data(odsdbConn, odsdbCursor, query_sql_tmp)
      #print("记录数：", len(result))
      convertList = obj.convert_data(result, tableName)
      convertRow = len(convertList)
      print("转换数：", convertRow)
      #break
      #row += 1
      # 批量写入数据
      if convertRow > 0 :
        rest = obj.exec_batch(odsdbConn, odsdbCursor, convertList)
        print('操作成功数：', rest)
      #if row > 0 and (row % 100 == 0 or row == maxRow):

    odsdbConn.close()

    return row


  #更新源表
  def updateOriNewest(self, odsdbConn, odsdbCursor):
    #df = pd.read_excel(r'D:\\EJU\\项目相关文件\\新房高德信息update.xlsx', sheet_name=0)
    dict = {"ori_newest_info_bk_20210105":"贝壳", "ori_newest_info_ftx_20210105":"房天下", "ori_newest_info_jl_20210105":"居里", "ori_newest_info_jw_20210105":"吉屋", "ori_newest_info_sh_20210105":"搜狐"}
    dictItems = dict.items();
    for item in dictItems :
      tableName = item[0]
      platform = item[1]
      print("开始更新【%s】数据#####################################" % platform)
      count_sql = "select count(id) from ori_newest_info_update WHERE platform = '%s' " % platform
      query_sql = "SELECT * from ori_newest_info_update WHERE platform = '%s' order by id limit %d, %d "

      #df = df.loc[df['platform'] == platform]
      #print(len(df))

      obj.exec(odsdbConn, odsdbCursor, count_sql, query_sql, platform, tableName)

  #更新目标表
  def updateTarNewest(self, odsdbConn, odsdbCursor):

    #df = pd.read_excel(r'D:\\EJU\\项目相关文件\\新房高德信息update.xlsx', sheet_name=0)
    #df = df.loc[df['platform'] == platform]
    #print(len(df))
    pass


if __name__ == "__main__":
  #mysql
  odsdbConn = pymysql.connect(host='172.28.56.90', user='mysql', password='egSQ7HhxajHZjvdX', port=3306, database='odsdb',charset="utf8")
  odsdbCursor = odsdbConn.cursor()

  #记录执行开始时间
  str_time = int(time.time())

  #实例化类
  obj = UpdateNewestInfo()
  obj.updateOriNewest(odsdbConn, odsdbCursor)


  end_time = int(time.time())
  print("程序执行时间（秒）：", end_time - str_time)

  """
  #结果验证
  SELECT aa.gd_city,bb.gd_city, aa.gd_county,bb.gd_county, aa.gd_lnglat,bb.gd_lnglat, aa.gd_office_name,bb.gd_office_name, aa.is_accurate,bb.is_accurate 
  from ori_newest_info_bk_20210105 aa inner join ori_newest_info_update bb on aa.id = bb.id and bb.platform = '贝壳';
  
  SELECT aa.gd_city,bb.gd_city, aa.gd_county,bb.gd_county, aa.gd_lnglat,bb.gd_lnglat, aa.gd_office_name,bb.gd_office_name, aa.is_accurate,bb.is_accurate 
  from ori_newest_info_ftx_20210105 aa inner join ori_newest_info_update bb on aa.id = bb.id and bb.platform = '房天下';
  
  """
